﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using 学生册成绩管理系统.DAL;
using 学生册成绩管理系统.Models;

namespace 学生册成绩管理系统.BLL
{
    public class StudentService
    {
        public List<Student> Login(Student student)
        {
            SqlHelper sqlHelper = new SqlHelper();

            //SqlConnection sqlConnection = new SqlConnection(sqlHelper.ConnectionString);
            //DataSet dataSet = sqlHelper.ExecuteDataSet(sqlConnection, CommandType.Text, $"SELECT  [Id] ,[Name] ,[Password] ,[Sex],[City],[InsertDate] FROM [Student] Where Name = '{Student.Name}' And Password = '{Student.Password}'");
            DataSet dataSet = sqlHelper.ExecuteDataSet($"SELECT  [Id] ,[Name] ,[Password] ,[Sex],[City],[InsertDate] FROM [Student] Where Name = '{student.Name}' And Password = '{student.Password}'");


            // 这里我解释下：就是第一张表中的第一行数据
            //var row = dataSet.Tables[0].Rows[0];
            // 因为DataTables是一个由多张表组成的集合

            // 使用泛型集合，使ORM数据库和数据实体的映射关系实现
            // ORM映射：负责将 DataSet 中的数据映射到 Student 类的实例上
            // 通俗易懂来讲：将查询结果从 DataTable 的行转换为 Student 对象
            List<Student> students = sqlHelper.DataSetToList<Student>(dataSet);

            return students;
        }

        public int Insert(Student student)
        {
            int count = -1;
            SqlHelper sqlHelper = new SqlHelper();
            if (student == null) { return count; }
            if (string.IsNullOrEmpty(student.Name)) { return count; }

            student.InsertDate = DateTime.Now;

            string sql = $"Insert Into Student (Name,Password,Sex,City,InsertDate,Role) Values (@Name,@Password,@Sex,@City,@InsertDate,@Role)";

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Name", student.Name),
                new SqlParameter("@Password", student.Password),
                new SqlParameter("@Sex", student.Sex),
                new SqlParameter("@City", student.City),
                new SqlParameter("@InsertDate", student.InsertDate),
                new SqlParameter("@Role", student.Role)
            };
            count = sqlHelper.ExecuteNonQuery(sql, parameters);

            return count;
        }
    }
}
